﻿Imports System.Data
Imports System.Text
Imports Mysoft.Map.Data
Imports Mysoft.Slxt.Business
Imports Mysoft.PubProject.Business
Imports Mysoft.Map.Excel
Imports System.Text.RegularExpressions

Public Class ImportActivityTmp
    Inherits Mysoft.Map.Application.Security.AppPage

    Private dtErrorTable As DataTable

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '在此处放置初始化页的用户代码
        dtErrorTable = New DataTable
        dtErrorTable.Columns.Add("rowid", GetType(String))
        dtErrorTable.Columns.Add("SendName", GetType(String))
        dtErrorTable.Columns.Add("ErrorInfo", GetType(String))
        If Not IsPostBack Then
            Repeater1.BindMode = 1
            Repeater1.DataTable = dtErrorTable
            Repeater1.DataBind()
        End If
    End Sub

    Private Sub ok_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ok.Click
        _Reflesh.Text = "0"
        ErrorInfo.Text = ""
        RightCount.Text = "0"
        ErrorCount.Text = "0"

        Dim strFileName, strModuleType As String
        Dim strPath As String = "\TempFiles"      '相对路径

        Randomize()
        strFileName = Server.MapPath(strPath) & "\Import_" & CInt(Int(&H7FFFFFFF * Rnd()) + 1).ToString & ".xls"
        Do While IO.File.Exists(strFileName)
            strFileName = Server.MapPath(strPath) & "\Import_" & CInt(Int(&H7FFFFFFF * Rnd()) + 1).ToString & ".xls"
        Loop
        fImport.PostedFile.SaveAs(strFileName)
        Dim dtTmp As DataTable
        dtTmp = GetDataTable(strFileName)
        IO.File.Delete(strFileName)

        Dim iCount As Integer
        iCount = dtTmp.Rows.Count
        If dtTmp.Columns.Count < 3 Then
            Me._Reflesh.Text = "1"
            Me.ErrorInfo.Text = "指定的导入文件（Excel）格式不正确！"
            Return
        End If
        If iCount < 1 Then
            Me._Reflesh.Text = "1"
            Me.ErrorInfo.Text = "导入文件中没有客户信息！"
            Return
        End If
        If dtTmp.Columns(0).ColumnName <> "客户名称" Or dtTmp.Columns(1).ColumnName <> "手机" Or dtTmp.Columns(2).ColumnName <> "Email" Then
            Me._Reflesh.Text = "1"
            Me.ErrorInfo.Text = "指定的导入文件（Excel）格式不正确！"
            Return
        End If

        ImportActivityTmp(dtTmp)
    End Sub

    Function GetDataTable(ByVal strExcelFile As String, Optional ByVal strSheet As String = "Sheet1") As DataTable
        Dim i, j, intMaxRow, intMaxCol As Integer
        Dim dcTemp As DataColumn
        Dim drTemp As DataRow
        Dim dtTemp As New DataTable
        Dim wb As New ExcelWookbook
        Dim ws As ExcelWorksheet
        Try
            wb.LoadXls(strExcelFile)
            ws = wb.Worksheets(strSheet)
        Catch ex As Exception
            Return dtTemp
        End Try
        ws.ShowSheetHeaders = True

        If ws.Rows.Count > 0 Then
            intMaxCol = ws.Rows(0).AllocatedCells.Count
        Else
            Return dtTemp
        End If

        For i = 0 To intMaxCol - 1
            dcTemp = New DataColumn(ws.Rows(0).Cells(i).Value)
            dtTemp.Columns.Add(dcTemp)
        Next

        intMaxRow = ws.Rows.Count
        For i = 1 To intMaxRow - 1
            drTemp = dtTemp.NewRow
            For j = 0 To intMaxCol - 1
                drTemp.Item(j) = ws.Rows(i).Cells(j).Value
            Next
            dtTemp.Rows.Add(drTemp)
        Next

        ws = Nothing
        wb = Nothing
        GC.Collect()

        Return dtTemp
    End Function

    Function ImportActivityTmp(ByVal DTTemp As DataTable) As Boolean
        Dim i, iCol, iCount, intRightCount, intErrorCount As Integer
        Dim strSQL, strActTmpGUID, strSendName, strLocation, strError, strActExecGUID, strlocationList As String
        strlocationList = ""
        Dim RoomXMLBuild As System.Text.StringBuilder = New StringBuilder

        intRightCount = 0           '正确数
        intErrorCount = 0           '错误数
        iCount = 0
        If Request.QueryString("ActType") = "短信" Then
            iCol = 1
        Else
            iCol = 2
        End If
        strActExecGUID = Request.QueryString("ActExecGUID")         '执行GUID

        RoomXMLBuild.Append(" DELETE P_ActivityReady WHERE ActExecGUID='" & strActExecGUID & "';")
        dtErrorTable.Rows.Clear()

        For i = 0 To DTTemp.Rows.Count - 1
            strSendName = Trim(DTTemp.Rows(i).Item(0).ToString().Trim())
            strLocation = Trim(DTTemp.Rows(i).Item(iCol).ToString().Trim())

            If strSendName = "" And strLocation = "" Then
                Exit For
            End If

            strError = ""

            If strlocationList.IndexOf(strLocation + "；") > -1 Then
                If iCol = 1 Then
                    strError &= "；手机号码重复"
                Else
                    strError &= "；Email重复"
                End If

            End If

            strlocationList += Trim(DTTemp.Rows(i).Item(iCol).ToString().Trim()) + "；"

            '检查字段长度是否超长
            If GetByte(strSendName) > 60 Then
                strError &= "；客户名称字段超长"
            End If

            If GetByte(strSendName) > 100 Then
                If iCol = 1 Then
                    strError &= "；手机号码字段超长"
                Else
                    strError &= "；Email字段超长"
                End If
            End If

            If strSendName = "" Then
                strError &= "；客户名称不能为空"
            Else
                If strSendName.IndexOf("'") > -1 Then
                    strError &= "；客户名称不能包含单引号"
                End If
            End If
            If iCol = 1 Then
                If strLocation = "" Then
                    strError = "；手机号码不能为空"
                Else
                    If Not IsNumeric(strLocation) Then
                        strError &= "；手机号码应为数值"
                    Else
                        If strLocation.Length <> "11" Then
                            strError &= "；手机号码应为11位数值"
                        Else
                            If Left(strLocation, 1) <> "1" Then
                                strError &= "；手机号码必须以1开头"
                            End If
                        End If
                    End If
                End If
            Else
                If strLocation = "" Then
                    strError = "；Email不能为空"
                Else
                    If strLocation.IndexOf("'") > -1 Then
                        strError &= "；Email不能包含单引号"
                    Else
                        Dim FoundMatch As Boolean
                        Dim myRegExp As Regex
                        myRegExp = New Regex("^\w+([\-+.]\w+)*@\w+([\-.]\w+)*\.\w+([\-.]\w+)*$")
                        If myRegExp.IsMatch(strLocation) = False Then
                            strError &= "；Email格式不正确"
                        End If
                    End If
                End If
            End If

            '2）导入操作开始
            If strError <> String.Empty Then
                If strError.Substring(0, 1) = "；" Then
                    strError = strError.Substring(1)
                End If
                InsertdtErrorTable(strSendName, strError)
                intErrorCount = intErrorCount + 1
            Else
                strActTmpGUID = System.Guid.NewGuid().ToString()
                RoomXMLBuild.Append(" INSERT INTO P_ActivityReady (ActTmpGUID,SendName,Location,ActExecGUID) VALUES ('")
                RoomXMLBuild.Append(strActTmpGUID & "','" & strSendName & "','" & strLocation & "','" & strActExecGUID & "'); ")

                intRightCount = intRightCount + 1
                iCount = iCount + 1
            End If

            '因为SQL语句太长，所以20条执行一次
            If iCount >= 20 Then
                If MyDB.ExecSQL(RoomXMLBuild.ToString()) > 0 Then
                    RoomXMLBuild.Remove(0, RoomXMLBuild.Length)
                    iCount = 0
                End If
            End If
        Next
        If RoomXMLBuild.Length > 0 Then
            MyDB.ExecSQL(RoomXMLBuild.ToString())
        End If

        RightCount.Text = intRightCount
        ErrorCount.Text = intErrorCount

        _Reflesh.Text = "1"
        ErrorInfo.Text = ""
        Repeater1.BindMode = 1
        Repeater1.DataTable = dtErrorTable
        Repeater1.DataBind()
    End Function

    '插入错误表
    Function InsertdtErrorTable(ByVal strSendName As String, ByVal strErrorInfo As String)
        Dim drRow As DataRow = dtErrorTable.NewRow()
        drRow.Item("rowid") = ""
        drRow.Item("SendName") = strSendName
        drRow.Item("ErrorInfo") = strErrorInfo
        dtErrorTable.Rows.Add(drRow)
    End Function

    '功能：取字节数（详细）
    '参数：strTxt   --文本
    '返回：字节数（整型）
    Public Function GetByte(ByVal strTxt As String) As Int64
        Dim WINNT_CHINESE As Boolean
        Dim intByte As Int64

        WINNT_CHINESE = (Len("文字") = 2)
        If WINNT_CHINESE Then
            Dim l, t, c, i As Int64
            l = Len(strTxt)
            t = l
            For i = 1 To l
                c = Asc(Mid(strTxt, i, 1))
                If c < 0 Then c = c + 65536
                If c > 255 Then
                    t = t + 1
                End If
            Next
            intByte = t
        Else
            intByte = Len(strTxt)
        End If
        If Err.Number <> 0 Then Err.Clear()

        Return intByte
    End Function

End Class